package br.com.iqt;

import br.com.iqt.zql.ZFromItem;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.StringTokenizer;
import java.util.Vector;

/**
 *
 * @author Arlino
 */
public class DriverStatisticsPostgresql implements DriverStatistics {

    private Dbms dbms;
    private String url;

    DriverStatisticsPostgresql(Dbms dbms) throws ClassNotFoundException {
        this.dbms = dbms;
        Class.forName(Dbms.driverPostgresql);
        this.url = "jdbc:postgresql://" + dbms.getServer() + ":" + dbms.getPort() + "/" + dbms.getDatabase();
    }

    @Override
    public boolean haveIndex(Vector tables, String column) throws SQLException {
        Connection connection = (Connection) DriverManager.getConnection(url, dbms.getUser(), dbms.getPassword());
        Statement stm = connection.createStatement();
        ResultSet rs;
        boolean result = false;
        for (Object object : tables) {
            ZFromItem tb = (ZFromItem) object;
            String table = tb.getTable();
            String scheme = tb.getSchema();

            if (scheme == null || scheme.isEmpty()) {
                scheme = "public";
            }
            String sql = "select * from pg_index where indrelid = (select oid from pg_class where upper(relname)= upper('" + table
                    + "') and relkind = 'r' and relnamespace = (select oid from pg_namespace where UPPER(nspname) = "
                    + "UPPER('" + scheme + "'))) and indkey[0] = (select attnum from pg_attribute where upper(attname) = upper('" + column
                    + "') and attrelid = (select oid from pg_class where upper(relname) = upper('" + table + "') and relkind = 'r' "
                    + "and relnamespace = (select oid from pg_namespace where UPPER(nspname) = UPPER('" + scheme + "'))))";

            rs = stm.executeQuery(sql);
            result = rs.next();

            if (result) {
                break;
            }
        }
    
        stm.close();
        connection.close();
        return result;
    }

    @Override
    public boolean isPrimaryKey(Vector tables, String column) throws SQLException {
        String user = this.dbms.getUser();
        String password = this.dbms.getPassword();
        Connection connection = (Connection) DriverManager.getConnection(this.url, user, password);
        
        Statement stm = connection.createStatement();;
        ResultSet rs = null;
        boolean result = false;
        for (Object object : tables) {
            ZFromItem fi = (ZFromItem) object;
            String table = fi.getTable();
            String scheme = fi.getSchema();
            if(scheme == null || scheme.equals(""))
                scheme = "public";

            String sql = "select * from pg_constraint where contype='p' and conrelid = (select oid from pg_class where "
                    + "relname= '" + table + "'  and relkind = 'r' and relnamespace = (select oid from pg_namespace "
                    + "where UPPER(nspname) = UPPER('" + scheme + "'))) and conkey[1] = (select attnum from pg_attribute where attname = "
                    + "'" + column + "' and attrelid = (select oid from pg_class where relname= '" + table + "'))";
            rs = stm.executeQuery(sql);
            result = rs.next();
            
            if (result) 
                break;
        }
        stm.close();
        connection.close();
        return result;
    }

    @Override
    public ZFromItem getTableWithPrimaryKey(Vector tables, String column) throws SQLException {
        String user = this.dbms.getUser();
        String password = this.dbms.getPassword();
        Connection connection = (Connection) DriverManager.getConnection(this.url, user, password);

        Statement stm = connection.createStatement();
        ResultSet rs = null;
        ZFromItem tableWithPrimaryKey = null;
        for (Object object : tables) {
            ZFromItem tb = (ZFromItem) object;
            String table = tb.getTable();
            String scheme = tb.getSchema();
            if(scheme == null || scheme.equals(""))
                scheme = "public";

            String sql = "select * from pg_constraint where contype='p' and conrelid = (select oid from pg_class where "
                    + "relname= '" + table + "'  and relkind = 'r' and relnamespace = (select oid from pg_namespace "
                    + "where UPPER(nspname) = UPPER('" + scheme + "'))) and conkey[1] = (select attnum from pg_attribute where attname = "
                    + "'" + column + "' and attrelid = (select oid from pg_class where relname= '" + table + "'))";
            rs = stm.executeQuery(sql);

            if (rs.next()) {
                tableWithPrimaryKey = tb;
                break;
            }
        }
        stm.close();
        connection.close();
        return tableWithPrimaryKey;
    }

    @Override
    public boolean isForingkeyFromTable1InTabel2(ZFromItem table, String column, Vector from) throws SQLException {
        Connection connection = (Connection) DriverManager.getConnection(this.url, dbms.getUser(), dbms.getPassword());
        Statement stm = connection.createStatement();

        boolean result = false;
        for (Object obj : from) {
            ZFromItem tb = (ZFromItem) obj;

            String schema1 = table.getSchema();
            if (schema1 == null || schema1.equals("")) {
                schema1 = "public";
            }
            String table1 = table.getTable();

            String schema2 = tb.getSchema();
            if (schema2 == null || schema2.equals("")) {
                schema2 = "public";
            }
            String table2 = tb.getTable();

            if (!table1.equalsIgnoreCase(table2) || schema1.equalsIgnoreCase(schema2)) {
                String sql = "select * from pg_constraint where contype='f' and conrelid = (select oid from pg_class where "
                        + "UPPER(relname)=UPPER('" + table2 + "') and relkind = 'r' and relnamespace = (select oid from "
                        + "pg_namespace where UPPER(nspname)=UPPER('" + schema2 + "'))) and confkey[1] = (select attnum from "
                        + "pg_attribute where UPPER(attname) = UPPER('" + column + "') and attrelid = (select oid from "
                        + "pg_class where UPPER(relname)=UPPER('" + table1 + "') and relkind = 'r' and relnamespace = "
                        + "(select oid from pg_namespace where UPPER(nspname) = UPPER('" + schema1 + "'))))";
                ResultSet rs = stm.executeQuery(sql);
                result = rs.next();
                
                if (result) {
                    break;
                }
            }
        }
        stm.close();
        connection.close();
        return result;
    }

    @Override
    public ZFromItem getTableWithForingkeyFromTable1InTabel2(ZFromItem table, String column, Vector from) throws SQLException {
        Connection connection = (Connection) DriverManager.getConnection(this.url, dbms.getUser(), dbms.getPassword());
        Statement stm = connection.createStatement();
        
        ZFromItem tableWithForingkey = null;
        for (Object obj : from) {
            ZFromItem tb = (ZFromItem) obj;

            String schema1 = table.getSchema();
            if (schema1 == null) {
                schema1 = "";
            }
            String table1 = table.getTable();

            String schema2 = tb.getSchema();
            if (schema2 == null) {
                schema1 = "";
            }
            String table2 = tb.getTable();

            if (!table1.equalsIgnoreCase(table2) || schema1.equalsIgnoreCase(schema2)) {
                String sql = "select * from pg_constraint where contype='f' and conrelid = (select oid from pg_class where "
                        + "UPPER(relname)=UPPER('" + table2 + "') and relkind = 'r' and relnamespace = (select oid from "
                        + "pg_namespace where UPPER(nspname)=UPPER('" + schema2 + "'))) and confkey[1] = (select attnum from "
                        + "pg_attribute where UPPER(attname) = UPPER('" + column + "') and attrelid = (select oid from "
                        + "pg_class where UPPER(relname)=UPPER('" + table1 + "') and relkind = 'r' and relnamespace = "
                        + "(select oid from pg_namespace where UPPER(nspname) = UPPER('" + schema1 + "'))))";
                ResultSet rs = stm.executeQuery(sql);

                if (rs.next()) {
                    tableWithForingkey = tb;
                }
            }
        }
        stm.close();
        connection.close();
        return tableWithForingkey;
    }

    @Override
    public ArrayList getExecutionPlan(String sql) throws SQLException {
        Connection connection = (Connection) DriverManager.getConnection(this.url, dbms.getUser(), dbms.getPassword());
        Statement statement = connection.createStatement();
        sql = "EXPLAIN " + sql;
        ResultSet resultSet = statement.executeQuery(sql);
        
        ArrayList list = new ArrayList();
        while (resultSet.next()) {
            list.add(resultSet.getString(1));
        }

        connection.close();
        return list;
    }

    @Override
    public String getRealRuntime(String sql) throws SQLException {
      Connection connection = (Connection) DriverManager.getConnection(this.url, dbms.getUser(), dbms.getPassword());
        Statement stm = connection.createStatement();
        
        long finalTime = 0;
        StringTokenizer stringTokenizer = new StringTokenizer(sql, ";");
        while(stringTokenizer.hasMoreElements()){
            String sqlAux = stringTokenizer.nextToken();
            String executionType;
            if(!sqlAux.toUpperCase().trim().startsWith("SELECT") || sqlAux.trim().toUpperCase().contains(" INTO "))
                executionType = "executeUpdate";
            else
                executionType = "executeQuery";
            
            long startTime = System.currentTimeMillis();
            if(executionType.equals("executeUpdate"))
                stm.executeUpdate(sqlAux);
            else
                stm.executeQuery(sqlAux);
            long endTime = System.currentTimeMillis();
            
            finalTime += endTime - startTime;
        }
        stm.close();
        connection.close();
        return (Long.toString(finalTime) + " ms");
    }
}
